﻿using Microsoft.EntityFrameworkCore;
using StuManager.model;
using System.Data;
using System.Data.SQLite;

namespace StuManager
{
    public partial class Form1 : Form
    {
        private SchoolContext? _context;
        private bool useEfCore = false;  // 决定是否使用 EF Core
        private string connectionString = "Data Source=students.db";

        public Form1()
        {
            InitializeComponent();

            if (useEfCore)
            {
                _context = new SchoolContext();
                _context.Database.Migrate();
            }

            LoadSchools();
            LoadClasses();
            LoadStudents();
            LoadLogs();
        }

        private void LoadSchools()
        {
            List<School> schoolsList = new List<School>();

            if (useEfCore)
            {
                // 使用 EF Core 加载数据
                schoolsList = _context.Schools.ToList();
            }
            else
            {
                // 使用 ADO.NET 加载数据
                using (var connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();
                    var command = new SQLiteCommand("SELECT * FROM Schools", connection);
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            schoolsList.Add(new School
                            {
                                SchoolId = reader.GetInt32(reader.GetOrdinal("SchoolId")),
                                Name = reader.GetString(reader.GetOrdinal("Name"))
                            });
                        }
                    }
                }
            }

            // 将统一的列表绑定到 DataGridView
            schoolDataGridView.DataSource = schoolsList;
        }


        private void LoadClasses()
        {
            List<Class> classesList = new List<Class>();

            if (useEfCore)
            {
                // 使用 EF Core 加载数据
                classesList = _context.Classes.Include(c => c.School).ToList();
            }
            else
            {
                // 使用 ADO.NET 加载数据
                using (var connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();
                    var command = new SQLiteCommand("SELECT * FROM Classes", connection);
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            classesList.Add(new Class
                            {
                                ClassId = reader.GetInt32(reader.GetOrdinal("ClassId")),
                                Name = reader.GetString(reader.GetOrdinal("Name")),
                                SchoolId = reader.GetInt32(reader.GetOrdinal("SchoolId"))
                            });
                        }
                    }
                }
            }

            // 将统一的列表绑定到 DataGridView
            classDataGridView.DataSource = classesList;
        }


        private void LoadStudents()
        {
            List<Student> studentsList = new List<Student>();

            if (useEfCore)
            {
                // 使用 EF Core 加载数据
                studentsList = _context.Students
                    .Include(s => s.Class)
                    .ThenInclude(c => c.School)
                    .ToList();
            }
            else
            {
                // 使用 ADO.NET 加载数据
                using (var connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();
                    var command = new SQLiteCommand("SELECT * FROM Students", connection);
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            studentsList.Add(new Student
                            {
                                StudentId = reader.GetInt32(reader.GetOrdinal("StudentId")),
                                Name = reader.GetString(reader.GetOrdinal("Name")),
                                Age = reader.GetInt32(reader.GetOrdinal("Age")),
                                ClassId = reader.GetInt32(reader.GetOrdinal("ClassId"))
                            });
                        }
                    }
                }
            }

            // 将统一的列表绑定到 DataGridView
            studentDataGridView.DataSource = studentsList;
        }


        private void LoadLogs()
        {
            List<Log> logsList = new List<Log>();

            if (useEfCore)
            {
                // 使用 EF Core 加载数据
                logsList = _context.Logs.ToList();
            }
            else
            {
                // 使用 ADO.NET 加载数据
                using (var connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();
                    var command = new SQLiteCommand("SELECT * FROM Logs", connection);
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            logsList.Add(new Log
                            {
                                LogId = reader.GetInt32(reader.GetOrdinal("LogId")),
                                Operation = reader.GetString(reader.GetOrdinal("Operation")),
                                OperationDate = reader.GetDateTime(reader.GetOrdinal("OperationDate"))
                            });
                        }
                    }
                }
            }

            // 将统一的列表绑定到 DataGridView
            logDataGridView.DataSource = logsList;
        }


        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            var schoolName = textBox1.Text;
            if (!string.IsNullOrEmpty(schoolName))
            {
                if (useEfCore)
                {
                    var school = new School { Name = schoolName };
                    _context.Schools.Add(school);
                    _context.SaveChanges();
                    _context.LogOperation($"Added School: {schoolName}");
                }
                else
                {
                    using (var connection = new SQLiteConnection(connectionString))
                    {
                        connection.Open();
                        var command = new SQLiteCommand("INSERT INTO Schools (Name) VALUES (@Name)", connection);
                        command.Parameters.AddWithValue("@Name", schoolName);
                        command.ExecuteNonQuery();

                        // 添加日志
                        command = new SQLiteCommand(
                            "INSERT INTO Logs (Operation, OperationDate) VALUES (@Operation, @OperationDate)", connection);
                        command.Parameters.AddWithValue("@Operation", $"Added School: {schoolName}");
                        command.Parameters.AddWithValue("@OperationDate", DateTime.Now); // 使用当前时间
                        command.ExecuteNonQuery();

                    }
                }
                LoadSchools();
                textBox1.Text = "";
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            var selectedSchool = schoolDataGridView.CurrentRow?.DataBoundItem as School;
            var schoolName = textBox1.Text;
            if (selectedSchool != null && !string.IsNullOrEmpty(schoolName))
            {
                if (useEfCore)
                {
                    selectedSchool.Name = schoolName;
                    _context.SaveChanges();
                    _context.LogOperation($"Updated School: {schoolName}");
                }
                else
                {
                    using (var connection = new SQLiteConnection(connectionString))
                    {
                        connection.Open();
                        var command = new SQLiteCommand("UPDATE Schools SET Name = @Name WHERE SchoolId = @SchoolId", connection);
                        command.Parameters.AddWithValue("@Name", schoolName);
                        command.Parameters.AddWithValue("@SchoolId", selectedSchool.SchoolId);
                        command.ExecuteNonQuery();

                        // 添加日志
                        command = new SQLiteCommand(
                            "INSERT INTO Logs (Operation, OperationDate) VALUES (@Operation, @OperationDate)", connection);
                        command.Parameters.AddWithValue("@OperationDate", DateTime.Now); // 使用当前时间作为操作日期
                        command.Parameters.AddWithValue("@Operation", $"Updated School: {schoolName}");
                        command.Parameters.AddWithValue("@OperationDate", DateTime.Now); // 使用当前时间作为操作日期
                        command.ExecuteNonQuery();

                    }
                }
                LoadSchools();
                textBox1.Text = "";
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            var selectedSchool = schoolDataGridView.CurrentRow?.DataBoundItem as School;
            if (selectedSchool != null)
            {
                if (useEfCore)
                {
                    _context.Schools.Remove(selectedSchool);
                    _context.SaveChanges();
                    _context.LogOperation($"Deleted School: {selectedSchool.Name}");
                }
                else
                {
                    using (var connection = new SQLiteConnection(connectionString))
                    {
                        connection.Open();
                        var command = new SQLiteCommand("DELETE FROM Schools WHERE SchoolId = @SchoolId", connection);
                        command.Parameters.AddWithValue("@SchoolId", selectedSchool.SchoolId);
                        command.ExecuteNonQuery();

                        // 添加日志
                        command = new SQLiteCommand(
                            "INSERT INTO Logs (Operation, OperationDate) VALUES (@Operation, @OperationDate)", connection);
                        command.Parameters.AddWithValue("@OperationDate", DateTime.Now); // 使用当前时间作为操作日期
                        command.Parameters.AddWithValue("@Operation", $"Deleted School: {selectedSchool.Name}");
                        command.ExecuteNonQuery();
                    }
                }
                LoadSchools();
                textBox1.Text = "";
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            var keyword = textBox1.Text.Trim();
            if (!string.IsNullOrEmpty(keyword))
            {
                if (useEfCore)
                {
                    var filteredSchools = _context.Schools
                        .Where(s => s.Name.Contains(keyword))
                        .ToList();
                    schoolDataGridView.DataSource = filteredSchools;
                }
                else
                {
                    var schools = new DataTable();
                    using (var connection = new SQLiteConnection(connectionString))
                    {
                        connection.Open();
                        var command = new SQLiteCommand("SELECT * FROM Schools WHERE Name LIKE @Keyword", connection);
                        command.Parameters.AddWithValue("@Keyword", "%" + keyword + "%");
                        var adapter = new SQLiteDataAdapter(command);
                        adapter.Fill(schools);
                    }
                    schoolDataGridView.DataSource = schools;
                }
            }
            else
            {
                LoadSchools();
            }
        }

        private void button8_Click(object sender, EventArgs e)
        {
            var className = textBox2.Text;
            var schoolId = Convert.ToInt32(textBox3.Text);

            if (!string.IsNullOrEmpty(className))
            {
                if (useEfCore)
                {
                    var classEntity = new Class { Name = className, SchoolId = schoolId };
                    _context.Classes.Add(classEntity);
                    _context.SaveChanges();
                    _context.LogOperation($"Added Class: {className}");
                }
                else
                {
                    using (var connection = new SQLiteConnection(connectionString))
                    {
                        connection.Open();
                        var command = new SQLiteCommand("INSERT INTO Classes (Name, SchoolId) VALUES (@Name, @SchoolId)", connection);
                        command.Parameters.AddWithValue("@Name", className);
                        command.Parameters.AddWithValue("@SchoolId", schoolId);
                        command.ExecuteNonQuery();

                        command = new SQLiteCommand(
                          "INSERT INTO Logs (Operation, OperationDate) VALUES (@Operation, @OperationDate)", connection);
                        command.Parameters.AddWithValue("@OperationDate", DateTime.Now); // 使用当前时间作为操作日期
                        command.Parameters.AddWithValue("@Operation", $"Added Class: {className}");
                        command.ExecuteNonQuery();
                    }
                }
                LoadClasses();
                textBox2.Text = "";
                textBox3.Text = "";
            }
        }

        private void button7_Click(object sender, EventArgs e)
        {
            var selectedClass = classDataGridView.CurrentRow?.DataBoundItem as Class;
            if (selectedClass != null)
            {
                var className = textBox2.Text;
                var schoolId = Convert.ToInt32(textBox3.Text);

                if (useEfCore)
                {
                    selectedClass.Name = className;
                    selectedClass.SchoolId = schoolId;
                    _context.SaveChanges();
                    _context.LogOperation($"Updated Class: {className}");
                }
                else
                {
                    using (var connection = new SQLiteConnection(connectionString))
                    {
                        connection.Open();
                        var command = new SQLiteCommand("UPDATE Classes SET Name = @Name, SchoolId = @SchoolId WHERE ClassId = @ClassId", connection);
                        command.Parameters.AddWithValue("@Name", className);
                        command.Parameters.AddWithValue("@SchoolId", schoolId);
                        command.Parameters.AddWithValue("@ClassId", selectedClass.ClassId);
                        command.ExecuteNonQuery();

                        command = new SQLiteCommand(
                            "INSERT INTO Logs (Operation, OperationDate) VALUES (@Operation, @OperationDate)", connection);
                        command.Parameters.AddWithValue("@OperationDate", DateTime.Now); // 使用当前时间作为操作日期
                        command.Parameters.AddWithValue("@Operation", $"Updated Class: {className}");
                        command.ExecuteNonQuery();
                    }
                }
                LoadClasses();
                textBox2.Text = "";
                textBox3.Text = "";
            }
        }

        private void button6_Click(object sender, EventArgs e)
        {
            var keyword = textBox2.Text.Trim();
            if (!string.IsNullOrEmpty(keyword))
            {
                if (useEfCore)
                {
                    var filteredClasses = _context.Classes
                        .Include(c => c.School)
                        .Where(c => c.Name.Contains(keyword) || c.School.Name.Contains(keyword))
                        .ToList();
                    classDataGridView.DataSource = filteredClasses;
                }
                else
                {
                    var classes = new DataTable();
                    using (var connection = new SQLiteConnection(connectionString))
                    {
                        connection.Open();
                        var command = new SQLiteCommand(
                            "SELECT * FROM Classes WHERE Name LIKE @Keyword OR SchoolId IN (SELECT Id FROM Schools WHERE Name LIKE @Keyword)",
                            connection);
                        command.Parameters.AddWithValue("@Keyword", "%" + keyword + "%");
                        var adapter = new SQLiteDataAdapter(command);
                        adapter.Fill(classes);
                    }
                    classDataGridView.DataSource = classes;
                }
            }
            else
            {
                LoadClasses();
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            var selectedClass = classDataGridView.CurrentRow?.DataBoundItem as Class;
            if (selectedClass != null)
            {
                if (useEfCore)
                {
                    _context.Classes.Remove(selectedClass);
                    _context.SaveChanges();
                    _context.LogOperation($"Deleted Class: {selectedClass.Name}");
                }
                else
                {
                    using (var connection = new SQLiteConnection(connectionString))
                    {
                        connection.Open();
                        var command = new SQLiteCommand("DELETE FROM Classes WHERE ClassId = @ClassId", connection);
                        command.Parameters.AddWithValue("@ClassId", selectedClass.ClassId);
                        command.ExecuteNonQuery();

                        command = new SQLiteCommand(
                            "INSERT INTO Logs (Operation, OperationDate) VALUES (@Operation, @OperationDate)", connection);
                        command.Parameters.AddWithValue("@OperationDate", DateTime.Now); // 使用当前时间作为操作日期
                        command.Parameters.AddWithValue("@Operation", $"Deleted Class: {selectedClass.Name}");
                        command.ExecuteNonQuery();
                    }
                }
                LoadClasses();
            }
        }

        private void button12_Click(object sender, EventArgs e)
        {
            var studentName = textBox5.Text;
            var age = Convert.ToInt32(textBox6.Text);
            var classId = Convert.ToInt32(textBox4.Text);

            if (!string.IsNullOrEmpty(studentName))
            {
                if (useEfCore)
                {
                    var student = new Student { Name = studentName, Age = age, ClassId = classId };
                    _context.Students.Add(student);
                    _context.SaveChanges();
                    _context.LogOperation($"Added Student: {studentName}");
                }
                else
                {
                    using (var connection = new SQLiteConnection(connectionString))
                    {
                        connection.Open();
                        var command = new SQLiteCommand("INSERT INTO Students (Name, Age, ClassId) VALUES (@Name, @Age, @ClassId)", connection);
                        command.Parameters.AddWithValue("@Name", studentName);
                        command.Parameters.AddWithValue("@Age", age);
                        command.Parameters.AddWithValue("@ClassId", classId);
                        command.ExecuteNonQuery();

                        command = new SQLiteCommand(
                            "INSERT INTO Logs (Operation, OperationDate) VALUES (@Operation, @OperationDate)", connection);
                        command.Parameters.AddWithValue("@OperationDate", DateTime.Now); // 使用当前时间作为操作日期
                        command.Parameters.AddWithValue("@Operation", $"Added Student: {studentName}");
                        command.ExecuteNonQuery();
                    }
                }
                LoadStudents();
                textBox5.Text = "";
                textBox6.Text = "";
                textBox4.Text = "";
            }
        }

        private void button11_Click(object sender, EventArgs e)
        {
            var selectedStudent = studentDataGridView.CurrentRow?.DataBoundItem as Student;
            if (selectedStudent != null)
            {
                var studentName = textBox5.Text;
                var age = Convert.ToInt32(textBox6.Text);
                var classId = Convert.ToInt32(textBox4.Text);

                if (useEfCore)
                {
                    selectedStudent.Name = studentName;
                    selectedStudent.Age = age;
                    selectedStudent.ClassId = classId;
                    _context.SaveChanges();
                    _context.LogOperation($"Updated Student: {studentName}");
                }
                else
                {
                    using (var connection = new SQLiteConnection(connectionString))
                    {
                        connection.Open();
                        var command = new SQLiteCommand("UPDATE Students SET Name = @Name, Age = @Age, ClassId = @ClassId WHERE StudentId = @StudentId", connection);
                        command.Parameters.AddWithValue("@Name", studentName);
                        command.Parameters.AddWithValue("@Age", age);
                        command.Parameters.AddWithValue("@ClassId", classId);
                        command.Parameters.AddWithValue("@StudentId", selectedStudent.StudentId);
                        command.ExecuteNonQuery();

                        command = new SQLiteCommand(
                            "INSERT INTO Logs (Operation, OperationDate) VALUES (@Operation, @OperationDate)", connection);
                        command.Parameters.AddWithValue("@OperationDate", DateTime.Now); // 使用当前时间作为操作日期
                        command.Parameters.AddWithValue("@Operation", $"Updated Student: {studentName}");
                        command.ExecuteNonQuery();
                    }
                }
                LoadStudents();
                textBox5.Text = "";
                textBox6.Text = "";
                textBox4.Text = "";
            }
        }

        private void button10_Click(object sender, EventArgs e)
        {
            var keyword = textBox5.Text.Trim();
            if (!string.IsNullOrEmpty(keyword))
            {
                if (useEfCore)
                {
                    var filteredStudents = _context.Students
                        .Include(s => s.Class)
                        .ThenInclude(c => c.School)
                        .Where(s => s.Name.Contains(keyword) || s.Class.Name.Contains(keyword) || s.Class.School.Name.Contains(keyword))
                        .ToList();
                    studentDataGridView.DataSource = filteredStudents;
                }
                else
                {
                    var students = new DataTable();
                    using (var connection = new SQLiteConnection(connectionString))
                    {
                        connection.Open();
                        var command = new SQLiteCommand(
                            @"SELECT Students.* 
                      FROM Students 
                      JOIN Classes ON Students.ClassId = Classes.Id 
                      JOIN Schools ON Classes.SchoolId = Schools.Id 
                      WHERE Students.Name LIKE @Keyword OR Classes.Name LIKE @Keyword OR Schools.Name LIKE @Keyword",
                            connection);
                        command.Parameters.AddWithValue("@Keyword", "%" + keyword + "%");
                        var adapter = new SQLiteDataAdapter(command);
                        adapter.Fill(students);
                    }
                    studentDataGridView.DataSource = students;
                }
            }
            else
            {
                LoadStudents();
            }
        }

        private void button9_Click(object sender, EventArgs e)
        {
            var selectedStudent = studentDataGridView.CurrentRow?.DataBoundItem as Student;
            if (selectedStudent != null)
            {
                if (useEfCore)
                {
                    _context.Students.Remove(selectedStudent);
                    _context.SaveChanges();
                    _context.LogOperation($"Deleted Student: {selectedStudent.Name}");
                }
                else
                {
                    using (var connection = new SQLiteConnection(connectionString))
                    {
                        connection.Open();
                        var command = new SQLiteCommand("DELETE FROM Students WHERE StudentId = @StudentId", connection);
                        command.Parameters.AddWithValue("@StudentId", selectedStudent.StudentId);
                        command.ExecuteNonQuery();

                        command = new SQLiteCommand(
                            "INSERT INTO Logs (Operation, OperationDate) VALUES (@Operation, @OperationDate)", connection);
                        command.Parameters.AddWithValue("@OperationDate", DateTime.Now); // 使用当前时间作为操作日期
                        command.Parameters.AddWithValue("@Operation", $"Deleted Student: {selectedStudent.Name}");
                        command.ExecuteNonQuery();
                    }
                }
                LoadStudents();
            }
        }
    }
}
